import sqlite3


class SQLStr:
    """
    获取数据库操作语句
    """

    Insert_Raw_Str = "INSERT INTO raw (raw_str, product_id) VALUES (?, ?)"
    Insert_Mean_Str = None
    Insert_Sensor_Str = None
    Insert_Product_Str = "INSERT INTO tb_product (p_name, label) VALUES (?, ?)"
    Update_Product_str = None
    Update_Sensor_str = None


def get_insert_str(table_name, col_list):
    """
    获取添加数据的sql语句字符串
    :param table_name: 表名
    :param col_list: 列名列表
    :return: sql语句字符串
    """
    return f"INSERT INTO {table_name} ({', '.join(col_list)}) VALUES ({', '.join(['?'] * len(col_list))})"


def get_update_str(table_name, col_list):
    """
    获取更新数据的sql语句字符串
    :param table_name: 表名
    :param col_list: 列名列表
    :param from_id: 从id开始更新，默认为0
    :param condition_str: 条件字符串，如"id=1"
    :return: sql语句字符串
    """
    if col_list:
        return f"UPDATE {table_name} SET {', '.join([f'{col} = ?' for col in col_list])} WHERE  id = ?"


# 数据库连接和线程锁
db_conn_list = []  # 使用队列作为数据库连接池
for _ in range(4):  # 初始化连接池
    conn_new = sqlite3.connect(r'.\sensor_data.db', check_same_thread=False)
    db_conn_list.append(conn_new)


# lock = threading.Lock()  # 线程锁

# 数据库操作装饰器，确保线程安全


# 定义插入数据的函数
def tread_one_row(sql_str, data):
    conn_one = db_conn_list[0]
    cursor = conn_one.cursor()
    cursor.execute(sql_str, data)
    data_id = cursor.lastrowid
    conn_one.commit()
    return data_id


def tread_many_row(sql_str, data_list, conn=None):
    if conn is None:
        conn_many = db_conn_list[1]
    else:
        conn_many = conn
    cursor = conn_many.cursor()
    cursor.executemany(sql_str, data_list)
    conn_many.commit()


# region 实际数据库处理方法


# 插入传感器信息
def insert_sensor(sensor_code, location, direction, product_id):
    """
    插入传感器信息
    :param sensor_code: 传感器编号
    :param location: 传感器位置
    :param direction: 传感器方向
    :param product_id: 产品id
    :return:
    """
    if not SQLStr.Insert_Sensor_Str:
        SQLStr.Insert_Sensor_Str = get_insert_str('tb_sensor', ["sensor_code", 'location', 'direction', 'product_id'])

    return tread_one_row(SQLStr.Insert_Sensor_Str, [sensor_code, location, direction, product_id])


def insert_product(product_name, product_code):
    """
    插入产品信息
    :param product_name: 产品名称
    :param product_code: 产品编号
    :return:
    """
    if not SQLStr.Insert_Product_Str:
        SQLStr.Insert_Product_Str = get_insert_str('tb_product', ["product_name", 'product_code'])
    return tread_one_row(SQLStr.Insert_Product_Str, [product_name, product_code])


def update_product(product_id, product_name, product_code):
    """
    更新产品信息
    :param product_id: 产品id
    :param product_name: 产品名称
    :param product_code: 产品编号
    :return:
    """
    if not SQLStr.Update_Product_str:
        SQLStr.Update_Product_str = get_update_str('tb_product', ["product_name", 'product_code'])
    tread_one_row(SQLStr.Update_Product_str, [product_name, product_code, product_id])


def update_sensor(sensor_id, sensor_code, location, direction, product_id):
    """
    更新传感器信息
    :param sensor_id: 传感器id
    :param sensor_code: 传感器编号
    :param location: 传感器位置
    :param direction: 传感器方向
    :param product_id: 产品id
    :return:
    """
    if not SQLStr.Update_Sensor_str:
        SQLStr.Update_Sensor_str = get_update_str('tb_sensor', ["sensor_code", 'location', 'direction', 'product_id'])
    tread_one_row(SQLStr.Update_Sensor_str, [sensor_code, location, direction, product_id, sensor_id])

# endregion
